LibPQ.jl Insertion Example Does Not Work (Windows 2012)

#1

Hello, new to Julia, so please excuse my ignorance. Probably I should be able to figure out how to use LibPQ from the rest of the documentation, but I cannot even get the verbatim example (save for unique database connection information) of a data insertion to work.

Working with Julia 1.0.0 under Atom (Junolab) on a Windows 2012 R2 server.

So this example taken from https://invenia.github.io/LibPQ.jl/stable/index.html fails:

using LibPQ, DataStreams

conn = LibPQ.Connection(“dbname=postgres user=postgres password=XXXXXXXXXXXX”)

result = execute(conn, “”"
CREATE TEMPORARY TABLE libpqjl_test (
no_nulls varchar(10) PRIMARY KEY,
yes_nulls varchar(10)
);
“”")

Data.stream!(
data,
LibPQ.Statement,
conn,
“INSERT INTO libpqjl_test (no_nulls, yes_nulls) VALUES ($1, $2);”,
)

close(conn)

with what seems an obvious error:

Press Enter to start Julia.
Starting Julia…
_
_ _ ()_ | Documentation: https://docs.julialang.org
() | () () |
_ _ | | __ _ | Type “?” for help, “]?” for Pkg help.
| | | | | | |/ ` | |
| | || | | | (| | | Version 1.0.0 (2018-08-08)
/ |_’|||_’_| | Official https://julialang.org/ release
|__/ |

[info | LibPQ]: CREATE TABLE
ERROR: LoadError: UndefVarError: data not defined
Stacktrace:
[1] top-level scope at none:0
[2] include_string(::Module, ::String, ::String) at .\loading.jl:1002
[3] (::getfield(Atom, Symbol("##118#123")){String,String,Module})() at C:\Users\jort.julia\packages\Atom\WSz3k\src\eval.jl:120
[4] withpath(::getfield(Atom, Symbol("##118#123")){String,String,Module}, ::String) at C:\Users\jort.julia\packages\CodeTools\hB4Hy\src\utils.jl:30
[5] withpath at C:\Users\jort.julia\packages\Atom\WSz3k\src\eval.jl:46 [inlined]
[6] #117 at C:\Users\jort.julia\packages\Atom\WSz3k\src\eval.jl:117 [inlined]
[7] hideprompt(::getfield(Atom, Symbol("##117#122")){String,String,Module}) at C:\Users\jort.julia\packages\Atom\WSz3k\src\repl.jl:76
[8] macro expansion at C:\Users\jort.julia\packages\Atom\WSz3k\src\eval.jl:116 [inlined]
[9] (::getfield(Atom, Symbol("##116#121")){Dict{String,Any}})() at .\task.jl:85
in expression starting at H:\Julia_Projects\ODM\libpq_test.jl:12

since “data” is not defined anywhere. How should it be defined so that it works with the parameters in the INSERT statement? I’ve been unable to find a clear example of LibPQ usage anywhere.

Many thanks!

#2

since “data” is not defined anywhere.

As I see - maybe the data is defined in the previous example.
imho: Not a beginner friendly.
You can give a feedback here: https://github.com/invenia/LibPQ.jl

My similar - working example - maybe you can use - Run Step by step - and compare the logs

using LibPQ, DataStreams , DataFrames
conn = LibPQ.Connection("dbname=$(ENV["PGDATABASE"]) user=$(ENV["PGUSER"])")

result = execute(conn, """
    DROP TABLE IF EXISTS libpqjl_test;
    CREATE TABLE libpqjl_test (
        lang   TEXT PRIMARY KEY,
        web    TEXT
    );
""")

langs = DataFrame( langv =["Julia", "Go", "Rust"],
                   webv  =["julialang.org", "golang.org", "www.rust-lang.org"]);

langs

Data.stream!(
    langs ,
    LibPQ.Statement,
    conn,
    "INSERT INTO libpqjl_test (lang, web ) VALUES (\$1, \$2);"
);

close(conn)

logs

_
_       _ _(_)_     |  Documentation: https://docs.julialang.org
(_)     | (_) (_)    |
_ _   _| |_  __ _   |  Type "?" for help, "]?" for Pkg help.
| | | | | | |/ _` |  |
| | |_| | | | (_| |  |  Version 1.0.0 (2018-08-08)
_/ |\__'_|_|_|\__'_|  |  Official https://julialang.org/ release
|__/                   |

julia> using LibPQ, DataStreams , DataFrames

julia> conn = LibPQ.Connection("dbname=$(ENV["PGDATABASE"]) user=$(ENV["PGUSER"])")
PostgreSQL connection (CONNECTION_OK) with parameters:
user = *******
password = ********************
dbname = *******
host = db
port = 5432
client_encoding = UTF8
application_name = LibPQ.jl
sslmode = prefer
sslcompression = 1
krbsrvname = postgres
target_session_attrs = any

julia> result = execute(conn, """
        DROP TABLE IF EXISTS libpqjl_test;
        CREATE TABLE libpqjl_test (
            lang   TEXT PRIMARY KEY,
            web    TEXT
        );
    """)
[info | LibPQ]: CREATE TABLE
PostgreSQL result

julia> langs = DataFrame( langv =["Julia", "Go", "Rust"],
                       webv  =["julialang.org", "golang.org", "www.rust-lang.org"]);

julia> langs
3×2 DataFrame
│ Row │ langv  │ webv              │
│     │ String │ String            │
├─────┼────────┼───────────────────┤
│ 1   │ Julia  │ julialang.org     │
│ 2   │ Go     │ golang.org        │
│ 3   │ Rust   │ www.rust-lang.org │

julia> Data.stream!(
        langs ,
        LibPQ.Statement,
        conn,
        "INSERT INTO libpqjl_test (lang, web ) VALUES (\$1, \$2);"
    );
[info | LibPQ]: 
[info | LibPQ]: 
[info | LibPQ]: INSERT 0 1
[info | LibPQ]: INSERT 0 1
[info | LibPQ]: INSERT 0 1

julia> close(conn)

julia>
#3

Got it to work, thank you!